{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Import temperature data from the DWD and process it\n",
    "\n",
    "This notebook pulls historical temperature data from the DWD server and formats it for future use in other projects. The data is delivered in a hourly frequencs in a .zip file for each of the available weather stations. To use the data, we need everythin in a single .csv-file, all stations side-by-side. Also, we need the daily average.\n",
    "\n",
    "To reduce computing time, we also crop all data earlier than 2007. \n",
    "\n",
    "Files should be executed in the following pipeline:\n",
    "* 1-dwd_konverter_download\n",
    "* 2-dwd_konverter_extract\n",
    "* 3-dwd_konverter_build_df\n",
    "* 4-dwd_konverter_final_processing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.) Final data processing\n",
    "We load in the data that has been saved in the last step, so we don't need to calculate everything again it we pause the project and come back later. \n",
    "### Data Cleaning\n",
    "The data contains some errors, which need to be cleaned. You can see, by looking at the output of main_df.describe() in the last cell, that the minimum teperature on some stations is -999. That means that there is no plausible measurement for this particular hour. We change this to np.nan, so that we can safely calculate the avarage values. \n",
    "### Change the frequency\n",
    "Finally we resample the data to daily means."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"9\" halign=\"left\">TT_TU</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>STATIONS_ID</th>\n",
       "      <th>3</th>\n",
       "      <th>44</th>\n",
       "      <th>71</th>\n",
       "      <th>73</th>\n",
       "      <th>78</th>\n",
       "      <th>91</th>\n",
       "      <th>96</th>\n",
       "      <th>102</th>\n",
       "      <th>125</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MESS_DATUM</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2011-12-31</th>\n",
       "      <td>NaN</td>\n",
       "      <td>3.88</td>\n",
       "      <td>2.76</td>\n",
       "      <td>1.19</td>\n",
       "      <td>4.30</td>\n",
       "      <td>2.43</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.80</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-01-01</th>\n",
       "      <td>NaN</td>\n",
       "      <td>10.90</td>\n",
       "      <td>8.14</td>\n",
       "      <td>4.03</td>\n",
       "      <td>10.96</td>\n",
       "      <td>10.27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-01-02</th>\n",
       "      <td>NaN</td>\n",
       "      <td>7.41</td>\n",
       "      <td>6.18</td>\n",
       "      <td>4.77</td>\n",
       "      <td>7.57</td>\n",
       "      <td>7.77</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.48</td>\n",
       "      <td>4.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-01-03</th>\n",
       "      <td>NaN</td>\n",
       "      <td>6.14</td>\n",
       "      <td>3.61</td>\n",
       "      <td>4.46</td>\n",
       "      <td>6.38</td>\n",
       "      <td>5.28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.63</td>\n",
       "      <td>3.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-01-04</th>\n",
       "      <td>NaN</td>\n",
       "      <td>5.80</td>\n",
       "      <td>2.48</td>\n",
       "      <td>4.45</td>\n",
       "      <td>5.46</td>\n",
       "      <td>4.57</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.85</td>\n",
       "      <td>1.94</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            TT_TU                                                 \n",
       "STATIONS_ID   3      44    71    73     78     91  96    102   125\n",
       "MESS_DATUM                                                        \n",
       "2011-12-31    NaN   3.88  2.76  1.19   4.30   2.43 NaN  3.80   NaN\n",
       "2012-01-01    NaN  10.90  8.14  4.03  10.96  10.27 NaN  9.01   NaN\n",
       "2012-01-02    NaN   7.41  6.18  4.77   7.57   7.77 NaN  6.48  4.66\n",
       "2012-01-03    NaN   6.14  3.61  4.46   6.38   5.28 NaN  5.63  3.51\n",
       "2012-01-04    NaN   5.80  2.48  4.45   5.46   4.57 NaN  5.85  1.94"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"9\" halign=\"left\">TT_TU</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>STATIONS_ID</th>\n",
       "      <th>3</th>\n",
       "      <th>44</th>\n",
       "      <th>71</th>\n",
       "      <th>73</th>\n",
       "      <th>78</th>\n",
       "      <th>91</th>\n",
       "      <th>96</th>\n",
       "      <th>102</th>\n",
       "      <th>125</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>1551.000000</td>\n",
       "      <td>4629.000000</td>\n",
       "      <td>3683.000000</td>\n",
       "      <td>4652.000000</td>\n",
       "      <td>4748.000000</td>\n",
       "      <td>4748.000000</td>\n",
       "      <td>267.000000</td>\n",
       "      <td>4490.000000</td>\n",
       "      <td>3935.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>10.103939</td>\n",
       "      <td>10.088153</td>\n",
       "      <td>8.411244</td>\n",
       "      <td>9.686855</td>\n",
       "      <td>9.872342</td>\n",
       "      <td>9.208837</td>\n",
       "      <td>13.193633</td>\n",
       "      <td>10.220345</td>\n",
       "      <td>8.466612</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>6.742460</td>\n",
       "      <td>6.653983</td>\n",
       "      <td>7.511708</td>\n",
       "      <td>7.849776</td>\n",
       "      <td>6.658399</td>\n",
       "      <td>7.124324</td>\n",
       "      <td>6.762327</td>\n",
       "      <td>6.076649</td>\n",
       "      <td>7.711229</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>-10.870000</td>\n",
       "      <td>-10.710000</td>\n",
       "      <td>-14.940000</td>\n",
       "      <td>-14.320000</td>\n",
       "      <td>-12.390000</td>\n",
       "      <td>-15.710000</td>\n",
       "      <td>-0.970000</td>\n",
       "      <td>-8.170000</td>\n",
       "      <td>-16.420000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>5.410000</td>\n",
       "      <td>5.250000</td>\n",
       "      <td>2.620000</td>\n",
       "      <td>3.397500</td>\n",
       "      <td>5.090000</td>\n",
       "      <td>3.870000</td>\n",
       "      <td>7.575000</td>\n",
       "      <td>5.790000</td>\n",
       "      <td>2.365000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>10.140000</td>\n",
       "      <td>10.320000</td>\n",
       "      <td>8.570000</td>\n",
       "      <td>9.900000</td>\n",
       "      <td>9.900000</td>\n",
       "      <td>9.230000</td>\n",
       "      <td>13.770000</td>\n",
       "      <td>10.200000</td>\n",
       "      <td>8.540000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>15.350000</td>\n",
       "      <td>15.380000</td>\n",
       "      <td>14.070000</td>\n",
       "      <td>16.080000</td>\n",
       "      <td>15.122500</td>\n",
       "      <td>14.820000</td>\n",
       "      <td>18.195000</td>\n",
       "      <td>15.260000</td>\n",
       "      <td>14.545000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>28.410000</td>\n",
       "      <td>28.450000</td>\n",
       "      <td>27.190000</td>\n",
       "      <td>26.940000</td>\n",
       "      <td>29.890000</td>\n",
       "      <td>27.550000</td>\n",
       "      <td>26.980000</td>\n",
       "      <td>27.330000</td>\n",
       "      <td>28.030000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   TT_TU                                                      \\\n",
       "STATIONS_ID          3            44           71           73           78    \n",
       "count        1551.000000  4629.000000  3683.000000  4652.000000  4748.000000   \n",
       "mean           10.103939    10.088153     8.411244     9.686855     9.872342   \n",
       "std             6.742460     6.653983     7.511708     7.849776     6.658399   \n",
       "min           -10.870000   -10.710000   -14.940000   -14.320000   -12.390000   \n",
       "25%             5.410000     5.250000     2.620000     3.397500     5.090000   \n",
       "50%            10.140000    10.320000     8.570000     9.900000     9.900000   \n",
       "75%            15.350000    15.380000    14.070000    16.080000    15.122500   \n",
       "max            28.410000    28.450000    27.190000    26.940000    29.890000   \n",
       "\n",
       "                                                                \n",
       "STATIONS_ID          91          96           102          125  \n",
       "count        4748.000000  267.000000  4490.000000  3935.000000  \n",
       "mean            9.208837   13.193633    10.220345     8.466612  \n",
       "std             7.124324    6.762327     6.076649     7.711229  \n",
       "min           -15.710000   -0.970000    -8.170000   -16.420000  \n",
       "25%             3.870000    7.575000     5.790000     2.365000  \n",
       "50%             9.230000   13.770000    10.200000     8.540000  \n",
       "75%            14.820000   18.195000    15.260000    14.545000  \n",
       "max            27.550000   26.980000    27.330000    28.030000  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"9\" halign=\"left\">TT_TU</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>STATIONS_ID</th>\n",
       "      <th>3</th>\n",
       "      <th>44</th>\n",
       "      <th>71</th>\n",
       "      <th>73</th>\n",
       "      <th>78</th>\n",
       "      <th>91</th>\n",
       "      <th>96</th>\n",
       "      <th>102</th>\n",
       "      <th>125</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MESS_DATUM</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2007-01-01</th>\n",
       "      <td>7.38</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.42</td>\n",
       "      <td>6.55</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.32</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2007-01-02</th>\n",
       "      <td>4.67</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.49</td>\n",
       "      <td>2.88</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.73</td>\n",
       "      <td>0.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2007-01-03</th>\n",
       "      <td>6.19</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.87</td>\n",
       "      <td>4.25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.12</td>\n",
       "      <td>0.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2007-01-04</th>\n",
       "      <td>7.69</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.82</td>\n",
       "      <td>5.85</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.34</td>\n",
       "      <td>4.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2007-01-05</th>\n",
       "      <td>7.78</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.47</td>\n",
       "      <td>6.03</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.20</td>\n",
       "      <td>3.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-27</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2.03</td>\n",
       "      <td>3.95</td>\n",
       "      <td>2.27</td>\n",
       "      <td>2.36</td>\n",
       "      <td>1.41</td>\n",
       "      <td>2.21</td>\n",
       "      <td>3.79</td>\n",
       "      <td>2.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-28</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.38</td>\n",
       "      <td>-0.59</td>\n",
       "      <td>-0.27</td>\n",
       "      <td>-0.07</td>\n",
       "      <td>-2.10</td>\n",
       "      <td>-0.05</td>\n",
       "      <td>2.32</td>\n",
       "      <td>-1.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-29</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.68</td>\n",
       "      <td>-2.04</td>\n",
       "      <td>-3.63</td>\n",
       "      <td>0.07</td>\n",
       "      <td>-2.41</td>\n",
       "      <td>-0.97</td>\n",
       "      <td>2.81</td>\n",
       "      <td>-4.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-30</th>\n",
       "      <td>NaN</td>\n",
       "      <td>5.92</td>\n",
       "      <td>1.88</td>\n",
       "      <td>-2.46</td>\n",
       "      <td>5.57</td>\n",
       "      <td>-1.26</td>\n",
       "      <td>3.78</td>\n",
       "      <td>5.97</td>\n",
       "      <td>-1.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-12-31</th>\n",
       "      <td>NaN</td>\n",
       "      <td>5.54</td>\n",
       "      <td>1.92</td>\n",
       "      <td>-0.41</td>\n",
       "      <td>4.05</td>\n",
       "      <td>-0.46</td>\n",
       "      <td>5.56</td>\n",
       "      <td>7.66</td>\n",
       "      <td>1.91</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4748 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            TT_TU                                                \n",
       "STATIONS_ID   3     44    71    73    78    91    96    102   125\n",
       "MESS_DATUM                                                       \n",
       "2007-01-01   7.38   NaN   NaN   NaN  7.42  6.55   NaN  8.32   NaN\n",
       "2007-01-02   4.67   NaN   NaN   NaN  4.49  2.88   NaN  6.73  0.51\n",
       "2007-01-03   6.19   NaN   NaN   NaN  4.87  4.25   NaN  7.12  0.91\n",
       "2007-01-04   7.69   NaN   NaN   NaN  7.82  5.85   NaN  8.34  4.43\n",
       "2007-01-05   7.78   NaN   NaN   NaN  7.47  6.03   NaN  8.20  3.92\n",
       "...           ...   ...   ...   ...   ...   ...   ...   ...   ...\n",
       "2019-12-27    NaN  2.03  3.95  2.27  2.36  1.41  2.21  3.79  2.78\n",
       "2019-12-28    NaN  0.38 -0.59 -0.27 -0.07 -2.10 -0.05  2.32 -1.29\n",
       "2019-12-29    NaN  0.68 -2.04 -3.63  0.07 -2.41 -0.97  2.81 -4.40\n",
       "2019-12-30    NaN  5.92  1.88 -2.46  5.57 -1.26  3.78  5.97 -1.32\n",
       "2019-12-31    NaN  5.54  1.92 -0.41  4.05 -0.46  5.56  7.66  1.91\n",
       "\n",
       "[4748 rows x 9 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from pathlib import Path\n",
    "\n",
    "# Import and export paths\n",
    "pkl_file = Path.cwd() / \"export_uncleaned\" / \"to_clean.pkl\"\n",
    "cleaned_file = Path.cwd() / \"export_cleaned\" / \"cleaned.csv\"\n",
    "\n",
    "# Read in the pickle file from the last cell\n",
    "cleaning_df = pd.read_pickle(pkl_file)\n",
    "\n",
    "\n",
    "# Replace all values with \"-999\", which indicate missing data\n",
    "cleaning_df.replace(to_replace=-999, value=np.nan, inplace=True)\n",
    "\n",
    "# Resample to daily frequency\n",
    "cleaning_df = cleaning_df.resample('D').mean().round(decimals=2)\n",
    "\n",
    "# Save as .csv\n",
    "cleaning_df.to_csv(cleaned_file, sep=\";\", decimal=\",\")\n",
    "\n",
    "display(cleaning_df.loc['2011-12-31':'2012-01-04'])\n",
    "display(cleaning_df.describe())\n",
    "display(cleaning_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
